package com.jianyushe.common.poi.excel.impl;

import com.jianyushe.common.poi.excel.PoiExcelUtil;
import com.jianyushe.common.util.JianYusheBaseConsoleUtil;
import com.jianyushe.common.util.StringUtil;
import com.jianyushe.jianyushebasecommon.util.ValidationUtils;
import lombok.Data;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IntList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;

/**
 * @author Create by Alvin
 * @Description PoiExcelUtil实现
 * @date 2022-04-19 21:15
 */
@Data
public class PoiExcelUtilImpl implements PoiExcelUtil {
    @Override
    public boolean readExcelByXssf() {
        return true;
    }

    @Override
    public boolean writeExcelByXssf() {
        return true;
    }

    /**
     * @param filePath 文件路径
     * @param fileName 文件名
     * @param startRow 起始行
     * @Description 读取到所有标记为'表名'的行 然后截取行间距 即为一张表
     * 循环生成每张表
     */
    @Override
    public void dbSqlGenerator(String filePath, String fileName, Integer startRow) {
        // 建表sql
        StringBuilder createTableSql = new StringBuilder();
        // 创建文件读入流
        try (FileInputStream fileInputStream = new FileInputStream(filePath + "\\" + fileName)) {
            Workbook workbook = new XSSFWorkbook(fileInputStream);
            Sheet sheet = workbook.getSheetAt(0);
            // 读取到所有标记为'表名'的行 然后截取行间距 即为一张表
            IntList tableRowNum = keyAtRowNum(sheet, TABLENAME);

            // 循环生成每张表
            // 取出下一行的位置(每次要取两个位置  当前位置  下一行位置)， 循环起始位置到下一行位置-1 即为一张表
            // 这里用数组方式即可 tableRowNum 从0开始， 判断是否超出下一行减1的位置
            final long startTimeMillis = System.currentTimeMillis();
            for (int i = 0; i < tableRowNum.size(); i++) {
                // 当前表所在行
                int currentTableRowNum = tableRowNum.get(i);
                JianYusheBaseConsoleUtil.printMessage("currentTableRowNum: " + currentTableRowNum);
                // 下一张表起始位置
                int nextTableRowNum;
                if (i != tableRowNum.size() - 1) {
                    // 非最后一张表
                    int nextTableIndex = i + 1;
                    nextTableRowNum = tableRowNum.get(nextTableIndex);
                    JianYusheBaseConsoleUtil.printMessage("nextTableRowNum: " + nextTableRowNum);
                } else {
                    nextTableRowNum = sheet.getLastRowNum();
                    JianYusheBaseConsoleUtil.printMessage("nextTableRowNum: " + nextTableRowNum);
                }

                createTableSql.append("\n CREATE TABLE ");
                String comment = "";
                String primary_key = " ";
                // 遍历从起始位置到下一张表Index - 1的所有行
                for (int j = currentTableRowNum; j < nextTableRowNum; j++) {
                    // 每张表第1行
                    if (j == currentTableRowNum) {
                        final Row row = sheet.getRow(j);
                        String tableName = row.getCell(1).getStringCellValue();
                        comment = row.getCell(3).getStringCellValue();
                        createTableSql.append(" `").append(tableName).append("` ").append(" ( \n");
                    } else if (j > currentTableRowNum + 1) {
                        // 跳过第二行
                        final Row row = sheet.getRow(j);
                        // 遍历取出该行的所有列, 从第二列开始
                        for (int k = 1; k < row.getPhysicalNumberOfCells(); k++) {
                            if (k == 1) {
                                if (j == currentTableRowNum + 2){
                                    primary_key = row.getCell(k).getStringCellValue();
                                }
                                createTableSql.append(" `").append(row.getCell(k)).append("` ");
                            } else if (k > 1 && k <= 3) {
                                createTableSql.append(" ").append(row.getCell(k));
                            } else if(k == 4){
                                createTableSql.append(" COMMENT '").append(row.getCell(k)).append("', \n");
                            }
                        }
                    }
                }
                createTableSql.append(" PRIMARY KEY (`").append(primary_key).append("`) \n");
                createTableSql.append(" ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  COMMENT='").append(comment).append("'; ").append("\n");
            }

            final long endTimeMillis = System.currentTimeMillis();
            JianYusheBaseConsoleUtil.printMessage("遍历完成，createTableSql: " + createTableSql.toString());
            JianYusheBaseConsoleUtil.printMessage("花销时间为： " + (double) (endTimeMillis - startTimeMillis) / 1000);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public IntList keyAtRowNum(Sheet sheet, String key) {
        ValidationUtils.notNull(key, "key不能为null");
        // 获取所有行
        final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        IntList tableRowNum = new IntList();
        for (int i = 0; i < physicalNumberOfRows; i++) {
            final Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            // 取出cell
            String cell = row.getCell(0).getStringCellValue();
            if (StringUtil.isNotEmpty(cell) && TABLENAME.equals(cell)) {
                tableRowNum.add(i);
            }
        }

        return tableRowNum;
    }
}
